﻿using System;
using System.Linq;
using System.Data.Linq;
using System.Collections.Generic;
using System.Text;
using HR.Appointments.DataLayer;

namespace HR.Appointments.Service.DataAccessLayer.DLinq
{
    internal class OrderDAO : IOrderDAO
    {
        #region IOrderDAO Members
        public OrderEntity GetInstance(int companyId, int orderId)
        {
            using (DAODataContext ctx = new DAODataContext())
            {
                var sql = from data in ctx.Appointments
                          join cmp in ctx.Companies on data.CompanyId equals cmp.CompanyId
                          where data.CompanyId == companyId
                          && data.AppointmentId == orderId
                          && !cmp.EndDate.HasValue
                          select data;

                return OrderEntity.Load(sql.FirstOrDefault());
            }
        }

        public List<OrderEntity> GetAllValidOrders(int companyId)
        {
            var result = new List<OrderEntity>();
            using (DAODataContext ctx = new DAODataContext())
            {
                var sql = from data in ctx.Appointments
                          join cmp in ctx.Companies on data.CompanyId equals cmp.CompanyId
                          where data.CompanyId == companyId
                          && data.Booked == false
                          && data.BookingDate.CompareTo(DateTime.Today) >= 0
                          && !cmp.EndDate.HasValue
                          orderby data.BookingDate, data.BookingHour, data.BookingMinute 
                          select data;

                foreach (HR.Appointments.DataLayer.Appointment row in sql)
                {
                    result.Add(OrderEntity.Load(row));
                }
            }
            return result;
        }

        public List<OrderEntity> GetByDate(int companyId, DateTime date)
        {
            var result = new List<OrderEntity>();
            using (DAODataContext ctx = new DAODataContext())
            {
                var sql = from data in ctx.Appointments
                          join cmp in ctx.Companies on data.CompanyId equals cmp.CompanyId
                          where data.CompanyId == companyId
                          && data.BookingDate.CompareTo(date) == 0
                          && !cmp.EndDate.HasValue
                          orderby data.BookingDate, data.BookingHour, data.BookingMinute
                          select data;

                //int x = 4;
                //var c = ctx.Appointments.Max(w => w.BookingDate);
                //var d = ctx.Appointments.Select(w => w.BookingDate == date && w.BookingHour == 1);

                foreach (HR.Appointments.DataLayer.Appointment table in sql)
                {
                    result.Add(OrderEntity.Load(table));
                }
            }
            return result;
        }

		public List<OrderEntity> GetByPhone(string phone)
		{
			var result = new List<OrderEntity>();
			using (DAODataContext ctx = new DAODataContext())
			{
				var sql = from data in ctx.Appointments
                          //join cmp in ctx.Companies on dat equals o.Key
                          join cmp in ctx.Companies on data.CompanyId equals cmp.CompanyId
                          where string.Compare(data.Phone, phone) == 0
                          && data.BookingDate.CompareTo(DateTime.Today) >= 0
                          && !cmp.EndDate.HasValue
                          orderby data.BookingDate, data.BookingHour, data.BookingMinute
						  select data;

				foreach (HR.Appointments.DataLayer.Appointment table in sql)
				{
					result.Add(OrderEntity.Load(table));
				}
			}
			return result;
		}
        
        public List<OrderEntity> GetNotifiableBooked()
        {
            var result = new List<OrderEntity>();
            using (DAODataContext ctx = new DAODataContext())
            {
                var sql = from data in ctx.Appointments
                          join cmp in ctx.Companies on data.CompanyId equals cmp.CompanyId
                          where data.IsBooked == Convert.ToInt32(OrderStatus.CONFIRMED)
                          && (data.BookingDate.CompareTo(DateTime.Today.AddDays(1)) == 0
                          || data.BookingDate.CompareTo(DateTime.Today.AddDays(8)) == 0)
                          && !cmp.EndDate.HasValue
                          orderby data.BookingDate
                          select data;

                foreach (HR.Appointments.DataLayer.Appointment table in sql)
                {
                    result.Add(OrderEntity.Load(table));
                }
            }
            return result;
        }

        public List<OrderEntity> GetNotifiableOrdered(int companyId, int delayTime)
        {
            var result = new List<OrderEntity>();
            using (DAODataContext ctx = new DAODataContext())
            {
                var sql = from data in ctx.Appointments
                          join cmp in ctx.Companies on data.CompanyId equals cmp.CompanyId
                          where data.IsBooked == Convert.ToInt32(OrderStatus.UNCONFIRMED)
                          && data.CompanyId == companyId
                          && (data.CreationDate.AddDays(delayTime).CompareTo(DateTime.Today) > 0
                          || data.BookingDate.AddDays(-1).CompareTo(DateTime.Today) == 0)
                          && data.BookingDate.CompareTo(DateTime.Today) >= 0
                          && !cmp.EndDate.HasValue
                          orderby data.CompanyId, data.BookingDate
                          select data;

                foreach (HR.Appointments.DataLayer.Appointment table in sql)
                {
                    result.Add(OrderEntity.Load(table));
                }
            }
            return result;
        }

        public List<OrderEntity> GetUnSentByStatus(int companyId, OrderStatus status)
        {
            var result = new List<OrderEntity>();
            using (DAODataContext ctx = new DAODataContext())
            {
                var sql = from data in ctx.Appointments
                          join cmp in ctx.Companies on data.CompanyId equals cmp.CompanyId
                          where data.CompanyId == companyId
                          && data.IsBooked == Convert.ToInt32(status)
                          && data.BookingDate.CompareTo(DateTime.Today) >= 0
                          && data.Booked == false //booked means sent.
                          && !cmp.EndDate.HasValue
                          orderby data.BookingDate, data.BookingHour, data.BookingMinute 
                          select data;

                foreach (HR.Appointments.DataLayer.Appointment table in sql)
                {
                    result.Add(OrderEntity.Load(table));
                }
            }
            return result;
        }

        public void Insert(OrderEntity order)
        {
            using (DAODataContext ctx = new DAODataContext()) 
            {
                ctx.Appointments.InsertOnSubmit(order.ToData(null));
                ctx.SubmitChanges();
            }
        }

		public void Delete(int companyId, int orderId)
		{
			using (DAODataContext ctx = new DAODataContext())
			{
				var sql = from data in ctx.Appointments
						  where data.CompanyId == companyId
						  && data.AppointmentId == orderId
						  select data;
                
				if (sql.Count() != 0)
				{
					ctx.Appointments.DeleteOnSubmit(sql.FirstOrDefault());
					ctx.SubmitChanges();
				}
			}
		}

        public void Update(OrderEntity order)
        {
            using (DAODataContext ctx = new DAODataContext())
            {
                var sql = from data in ctx.Appointments
                          where data.CompanyId == order.Company.CompanyId
                          && data.AppointmentId == order.AppointmentId
                          select data;

                Appointment row = order.ToData(sql.FirstOrDefault());
                ctx.SubmitChanges();
            }
        }

        #endregion
    }
}
